{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# SELECT from WORLD"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdin",
     "output_type": "stream",
     "text": [
      " ····\n"
     ]
    }
   ],
   "source": [
    "import getpass\n",
    "import psycopg2\n",
    "from sqlalchemy import create_engine\n",
    "import pandas as pd\n",
    "pwd = getpass.getpass()\n",
    "engine = create_engine(\n",
    "    'postgresql+psycopg2://postgres:%s@localhost/sqlzoo' % (pwd))\n",
    "pd.set_option('display.max_rows', 20)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "world = pd.read_sql_table('world', engine)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Introduction\n",
    "\n",
    "[Read the notes about this table](https://sqlzoo.net/wiki/Read_the_notes_about_this_table.). Observe the result of running this SQL command to show the name, continent and population of all countries."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>continent</th>\n",
       "      <th>population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Asia</td>\n",
       "      <td>25500100.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Albania</td>\n",
       "      <td>Europe</td>\n",
       "      <td>2821977.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>Africa</td>\n",
       "      <td>38700000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>Europe</td>\n",
       "      <td>76098.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Angola</td>\n",
       "      <td>Africa</td>\n",
       "      <td>19183590.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190</th>\n",
       "      <td>Venezuela</td>\n",
       "      <td>South America</td>\n",
       "      <td>28946101.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>191</th>\n",
       "      <td>Vietnam</td>\n",
       "      <td>Asia</td>\n",
       "      <td>89708900.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>192</th>\n",
       "      <td>Yemen</td>\n",
       "      <td>Asia</td>\n",
       "      <td>25235000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193</th>\n",
       "      <td>Zambia</td>\n",
       "      <td>Africa</td>\n",
       "      <td>15023315.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>194</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>Africa</td>\n",
       "      <td>13061239.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>195 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            name      continent  population\n",
       "0    Afghanistan           Asia  25500100.0\n",
       "1        Albania         Europe   2821977.0\n",
       "2        Algeria         Africa  38700000.0\n",
       "3        Andorra         Europe     76098.0\n",
       "4         Angola         Africa  19183590.0\n",
       "..           ...            ...         ...\n",
       "190    Venezuela  South America  28946101.0\n",
       "191      Vietnam           Asia  89708900.0\n",
       "192        Yemen           Asia  25235000.0\n",
       "193       Zambia         Africa  15023315.0\n",
       "194     Zimbabwe         Africa  13061239.0\n",
       "\n",
       "[195 rows x 3 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "world.loc[:, ['name', 'continent', 'population']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Large Countries\n",
    "\n",
    "[How to use WHERE to filter records](https://sqlzoo.net/wiki/WHERE_filters). Show the name for the countries that have a population of at least 200 million. 200 million is 200000000, there are eight zeros."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Brazil</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>China</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>India</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>Indonesia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185</th>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              name\n",
       "23          Brazil\n",
       "35           China\n",
       "75           India\n",
       "76       Indonesia\n",
       "185  United States"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "world.loc[world['population']>=2e8, ['name']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Per capita GDP\n",
    "\n",
    "Give the `name` and the **per capita GDP** for those countries with a `population` of at least 200 million.\n",
    "\n",
    "> _HELP:How to calculate per capita GDP_   \n",
    "> per capita GDP is the GDP divided by the population GDP/population"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>pcgdp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Brazil</td>\n",
       "      <td>11115.264751</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>China</td>\n",
       "      <td>6121.710599</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>India</td>\n",
       "      <td>1504.793124</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>Indonesia</td>\n",
       "      <td>3482.020488</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185</th>\n",
       "      <td>United States</td>\n",
       "      <td>51032.294546</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              name         pcgdp\n",
       "23          Brazil  11115.264751\n",
       "35           China   6121.710599\n",
       "75           India   1504.793124\n",
       "76       Indonesia   3482.020488\n",
       "185  United States  51032.294546"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(world.assign(pcgdp=world['gdp']/world['population'])\n",
    "      .loc[world['population']>=2e8, ['name', 'pcgdp']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 4. South America In millions\n",
    "\n",
    "Show the `name` and `population` in millions for the countries of the `continent` 'South America'. Divide the population by 1000000 to get population in millions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>popl</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>42.669500</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Bolivia</td>\n",
       "      <td>10.027254</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Brazil</td>\n",
       "      <td>202.794000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Chile</td>\n",
       "      <td>17.773000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Colombia</td>\n",
       "      <td>47.662000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>Ecuador</td>\n",
       "      <td>15.774200</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>Guyana</td>\n",
       "      <td>0.784894</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>Paraguay</td>\n",
       "      <td>6.783374</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>134</th>\n",
       "      <td>Peru</td>\n",
       "      <td>30.475144</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144</th>\n",
       "      <td>Saint Vincent and the Grenadines</td>\n",
       "      <td>0.109000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>164</th>\n",
       "      <td>Suriname</td>\n",
       "      <td>0.534189</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>186</th>\n",
       "      <td>Uruguay</td>\n",
       "      <td>3.286314</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190</th>\n",
       "      <td>Venezuela</td>\n",
       "      <td>28.946101</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 name        popl\n",
       "6                           Argentina   42.669500\n",
       "20                            Bolivia   10.027254\n",
       "23                             Brazil  202.794000\n",
       "34                              Chile   17.773000\n",
       "36                           Colombia   47.662000\n",
       "50                            Ecuador   15.774200\n",
       "70                             Guyana    0.784894\n",
       "133                          Paraguay    6.783374\n",
       "134                              Peru   30.475144\n",
       "144  Saint Vincent and the Grenadines    0.109000\n",
       "164                          Suriname    0.534189\n",
       "186                           Uruguay    3.286314\n",
       "190                         Venezuela   28.946101"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(world.assign(popl=world['population']/1e6)\n",
    "      .loc[world['continent']=='South America', ['name', 'popl']])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 5. France, Germany, Italy\n",
    "\n",
    "Show the `name` and `population` for France, Germany, Italy"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>population</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>France</td>\n",
       "      <td>65906000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>Germany</td>\n",
       "      <td>80716000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>Italy</td>\n",
       "      <td>60782668.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       name  population\n",
       "59   France  65906000.0\n",
       "63  Germany  80716000.0\n",
       "81    Italy  60782668.0"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "world.loc[world['name'].isin(['France', 'Germany', 'Italy']), ['name', 'population']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 6. United\n",
    "\n",
    "Show the countries which have a `name` that includes the word 'United'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>183</th>\n",
       "      <td>United Arab Emirates</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>184</th>\n",
       "      <td>United Kingdom</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185</th>\n",
       "      <td>United States</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     name\n",
       "183  United Arab Emirates\n",
       "184        United Kingdom\n",
       "185         United States"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "world.loc[world['name'].str.contains('United'), ['name']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 7. Two ways to be big\n",
    "\n",
    "Two ways to be big: A country is **big** if it has an area of more than 3 million sq km or it has a population of more than 250 million.\n",
    "\n",
    "**Show the countries that are big by area or big by population. Show name, population and area.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>population</th>\n",
       "      <th>area</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Australia</td>\n",
       "      <td>2.354550e+07</td>\n",
       "      <td>7692024.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Brazil</td>\n",
       "      <td>2.027940e+08</td>\n",
       "      <td>8515767.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Canada</td>\n",
       "      <td>3.542752e+07</td>\n",
       "      <td>9984670.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>China</td>\n",
       "      <td>1.365370e+09</td>\n",
       "      <td>9596961.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>India</td>\n",
       "      <td>1.246160e+09</td>\n",
       "      <td>3166414.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>Indonesia</td>\n",
       "      <td>2.521648e+08</td>\n",
       "      <td>1904569.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>140</th>\n",
       "      <td>Russia</td>\n",
       "      <td>1.460000e+08</td>\n",
       "      <td>17125242.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185</th>\n",
       "      <td>United States</td>\n",
       "      <td>3.183200e+08</td>\n",
       "      <td>9826675.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              name    population        area\n",
       "8        Australia  2.354550e+07   7692024.0\n",
       "23          Brazil  2.027940e+08   8515767.0\n",
       "30          Canada  3.542752e+07   9984670.0\n",
       "35           China  1.365370e+09   9596961.0\n",
       "75           India  1.246160e+09   3166414.0\n",
       "76       Indonesia  2.521648e+08   1904569.0\n",
       "140         Russia  1.460000e+08  17125242.0\n",
       "185  United States  3.183200e+08   9826675.0"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "world.loc[(world['area']>3e6) | (world['population']>2.5e8),\n",
    "          ['name', 'population', 'area']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 8. One or the other (but not both)\n",
    "\n",
    "**Exclusive OR (XOR). Show the countries that are big by area (more than 3 million) or big by population (more than 250 million) but not both. Show name, population and area.**\n",
    "\n",
    "- Australia has a big area but a small population, it should be **included**.\n",
    "- Indonesia has a big population but a small area, it should be **included**.\n",
    "- China has a big population **and** big area, it should be **excluded**.\n",
    "- United Kingdom has a small population and a small area, it should be **excluded**."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>population</th>\n",
       "      <th>area</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Australia</td>\n",
       "      <td>23545500.0</td>\n",
       "      <td>7692024.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Brazil</td>\n",
       "      <td>202794000.0</td>\n",
       "      <td>8515767.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Canada</td>\n",
       "      <td>35427524.0</td>\n",
       "      <td>9984670.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76</th>\n",
       "      <td>Indonesia</td>\n",
       "      <td>252164800.0</td>\n",
       "      <td>1904569.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>140</th>\n",
       "      <td>Russia</td>\n",
       "      <td>146000000.0</td>\n",
       "      <td>17125242.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          name   population        area\n",
       "8    Australia   23545500.0   7692024.0\n",
       "23      Brazil  202794000.0   8515767.0\n",
       "30      Canada   35427524.0   9984670.0\n",
       "76   Indonesia  252164800.0   1904569.0\n",
       "140     Russia  146000000.0  17125242.0"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "world.loc[(world['area']>3e6)!=(world['population']>2.5e8),\n",
    "          ['name', 'population', 'area']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 9. Rounding\n",
    "\n",
    "Show the `name` and `population` in millions and the GDP in billions for the countries of the `continent` 'South America'. Use the [ROUND](https://sqlzoo.net/wiki/ROUND) function to show the values to two decimal places.\n",
    "\n",
    "**For South America show population in millions and GDP in billions both to 2 decimal places.**\n",
    "\n",
    "> _Millions and billions_    \n",
    "> Divide by 1000000 (6 zeros) for millions. Divide by 1000000000 (9 zeros) for billions."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>popl</th>\n",
       "      <th>gdp_</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Argentina</td>\n",
       "      <td>42.67</td>\n",
       "      <td>477.03</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>Bolivia</td>\n",
       "      <td>10.03</td>\n",
       "      <td>27.04</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Brazil</td>\n",
       "      <td>202.79</td>\n",
       "      <td>2254.11</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>34</th>\n",
       "      <td>Chile</td>\n",
       "      <td>17.77</td>\n",
       "      <td>268.31</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36</th>\n",
       "      <td>Colombia</td>\n",
       "      <td>47.66</td>\n",
       "      <td>369.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50</th>\n",
       "      <td>Ecuador</td>\n",
       "      <td>15.77</td>\n",
       "      <td>87.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>Guyana</td>\n",
       "      <td>0.78</td>\n",
       "      <td>2.85</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>Paraguay</td>\n",
       "      <td>6.78</td>\n",
       "      <td>25.94</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>134</th>\n",
       "      <td>Peru</td>\n",
       "      <td>30.48</td>\n",
       "      <td>204.68</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>144</th>\n",
       "      <td>Saint Vincent and the Grenadines</td>\n",
       "      <td>0.11</td>\n",
       "      <td>0.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>164</th>\n",
       "      <td>Suriname</td>\n",
       "      <td>0.53</td>\n",
       "      <td>5.01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>186</th>\n",
       "      <td>Uruguay</td>\n",
       "      <td>3.29</td>\n",
       "      <td>49.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190</th>\n",
       "      <td>Venezuela</td>\n",
       "      <td>28.95</td>\n",
       "      <td>382.42</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                 name    popl     gdp_\n",
       "6                           Argentina   42.67   477.03\n",
       "20                            Bolivia   10.03    27.04\n",
       "23                             Brazil  202.79  2254.11\n",
       "34                              Chile   17.77   268.31\n",
       "36                           Colombia   47.66   369.81\n",
       "50                            Ecuador   15.77    87.50\n",
       "70                             Guyana    0.78     2.85\n",
       "133                          Paraguay    6.78    25.94\n",
       "134                              Peru   30.48   204.68\n",
       "144  Saint Vincent and the Grenadines    0.11     0.69\n",
       "164                          Suriname    0.53     5.01\n",
       "186                           Uruguay    3.29    49.92\n",
       "190                         Venezuela   28.95   382.42"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(world.loc[world['continent']=='South America', ['name', 'population', 'gdp']]\n",
    "      .assign(popl=round(world['population']/1e6, 2),\n",
    "              gdp_=round(world['gdp']/1e9, 2))\n",
    "      .loc[:, ['name', 'popl', 'gdp_']]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 10. Trillion dollar economies\n",
    "\n",
    "Show the `name` and per-capita GDP for those countries with a GDP of at least one trillion (1000000000000; that is 12 zeros). Round this value to the nearest 1000.\n",
    "\n",
    "**Show per-capita GDP for the trillion dollar countries to the nearest $1000.**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>pcgdp</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Australia</td>\n",
       "      <td>66000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Brazil</td>\n",
       "      <td>11000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Canada</td>\n",
       "      <td>45000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>China</td>\n",
       "      <td>6000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59</th>\n",
       "      <td>France</td>\n",
       "      <td>40000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>63</th>\n",
       "      <td>Germany</td>\n",
       "      <td>42000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75</th>\n",
       "      <td>India</td>\n",
       "      <td>2000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81</th>\n",
       "      <td>Italy</td>\n",
       "      <td>33000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83</th>\n",
       "      <td>Japan</td>\n",
       "      <td>47000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109</th>\n",
       "      <td>Mexico</td>\n",
       "      <td>10000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>140</th>\n",
       "      <td>Russia</td>\n",
       "      <td>14000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159</th>\n",
       "      <td>South Korea</td>\n",
       "      <td>22000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>161</th>\n",
       "      <td>Spain</td>\n",
       "      <td>28000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>184</th>\n",
       "      <td>United Kingdom</td>\n",
       "      <td>39000.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>185</th>\n",
       "      <td>United States</td>\n",
       "      <td>51000.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               name    pcgdp\n",
       "8         Australia  66000.0\n",
       "23           Brazil  11000.0\n",
       "30           Canada  45000.0\n",
       "35            China   6000.0\n",
       "59           France  40000.0\n",
       "63          Germany  42000.0\n",
       "75            India   2000.0\n",
       "81            Italy  33000.0\n",
       "83            Japan  47000.0\n",
       "109          Mexico  10000.0\n",
       "140          Russia  14000.0\n",
       "159     South Korea  22000.0\n",
       "161           Spain  28000.0\n",
       "184  United Kingdom  39000.0\n",
       "185   United States  51000.0"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(world.assign(pcgdp=round(world['gdp']/(1000*world['population']), 0)*1000)\n",
    "      .loc[world['gdp']>1e12, ['name', 'pcgdp']]\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 11. Name and capital have the same length\n",
    "\n",
    "Greece has capital Athens.\n",
    "\n",
    "Each of the strings 'Greece', and 'Athens' has 6 characters.\n",
    "\n",
    "**Show the name and capital where the name and the capital have the same number of characters.**\n",
    "\n",
    "- You can use the [LENGTH](https://sqlzoo.net/wiki/LENGTH) function to find the number of characters in a string"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>capital</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>Algiers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Angola</td>\n",
       "      <td>Luanda</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Armenia</td>\n",
       "      <td>Yerevan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>Botswana</td>\n",
       "      <td>Gaborone</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>30</th>\n",
       "      <td>Canada</td>\n",
       "      <td>Ottowa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>Djibouti</td>\n",
       "      <td>Djibouti</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51</th>\n",
       "      <td>Egypt</td>\n",
       "      <td>Cairo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>55</th>\n",
       "      <td>Estonia</td>\n",
       "      <td>Tallinn</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57</th>\n",
       "      <td>Fiji</td>\n",
       "      <td>Suva</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61</th>\n",
       "      <td>Gambia</td>\n",
       "      <td>Banjul</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>62</th>\n",
       "      <td>Georgia</td>\n",
       "      <td>Tbilisi</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64</th>\n",
       "      <td>Ghana</td>\n",
       "      <td>Accra</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>65</th>\n",
       "      <td>Greece</td>\n",
       "      <td>Athens</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>Luxembourg</td>\n",
       "      <td>Luxembourg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>107</th>\n",
       "      <td>Mauritania</td>\n",
       "      <td>Nouakchott</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>133</th>\n",
       "      <td>Paraguay</td>\n",
       "      <td>Asunción</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>134</th>\n",
       "      <td>Peru</td>\n",
       "      <td>Lima</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>136</th>\n",
       "      <td>Poland</td>\n",
       "      <td>Warsaw</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>140</th>\n",
       "      <td>Russia</td>\n",
       "      <td>Moscow</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>141</th>\n",
       "      <td>Rwanda</td>\n",
       "      <td>Kigali</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146</th>\n",
       "      <td>San Marino</td>\n",
       "      <td>San Marino</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>153</th>\n",
       "      <td>Singapore</td>\n",
       "      <td>Singapore</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>169</th>\n",
       "      <td>Taiwan</td>\n",
       "      <td>Taipei</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>174</th>\n",
       "      <td>Togo</td>\n",
       "      <td>Lomé</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>178</th>\n",
       "      <td>Turkey</td>\n",
       "      <td>Ankara</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193</th>\n",
       "      <td>Zambia</td>\n",
       "      <td>Lusaka</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           name     capital\n",
       "2       Algeria     Algiers\n",
       "4        Angola      Luanda\n",
       "7       Armenia     Yerevan\n",
       "22     Botswana    Gaborone\n",
       "30       Canada      Ottowa\n",
       "47     Djibouti    Djibouti\n",
       "51        Egypt       Cairo\n",
       "55      Estonia     Tallinn\n",
       "57         Fiji        Suva\n",
       "61       Gambia      Banjul\n",
       "62      Georgia     Tbilisi\n",
       "64        Ghana       Accra\n",
       "65       Greece      Athens\n",
       "98   Luxembourg  Luxembourg\n",
       "107  Mauritania  Nouakchott\n",
       "133    Paraguay    Asunción\n",
       "134        Peru        Lima\n",
       "136      Poland      Warsaw\n",
       "140      Russia      Moscow\n",
       "141      Rwanda      Kigali\n",
       "146  San Marino  San Marino\n",
       "153   Singapore   Singapore\n",
       "169      Taiwan      Taipei\n",
       "174        Togo        Lomé\n",
       "178      Turkey      Ankara\n",
       "193      Zambia      Lusaka"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "world.loc[world['name'].str.len()==world['capital'].str.len(),\n",
    "          ['name', 'capital']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 12. Matching name and capital\n",
    "\n",
    "The capital of Sweden is Stockholm. Both words start with the letter 'S'.\n",
    "\n",
    "**Show the name and the capital where the first letters of each match. Don't include countries where the name and the capital are the same word.**\n",
    "\n",
    "- You can use the function [LEFT](https://sqlzoo.net/wiki/LEFT) to isolate the first character.\n",
    "- You can use <> as the **NOT EQUALS** operator."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>capital</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Algeria</td>\n",
       "      <td>Algiers</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>Andorra la Vella</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Barbados</td>\n",
       "      <td>Bridgetown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>Belize</td>\n",
       "      <td>Belmopan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>Brazil</td>\n",
       "      <td>Brasília</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Brunei</td>\n",
       "      <td>Bandar Seri Begawan</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>27</th>\n",
       "      <td>Burundi</td>\n",
       "      <td>Bujumbura</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>47</th>\n",
       "      <td>Djibouti</td>\n",
       "      <td>Djibouti</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>67</th>\n",
       "      <td>Guatemala</td>\n",
       "      <td>Guatemala City</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70</th>\n",
       "      <td>Guyana</td>\n",
       "      <td>Georgetown</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>88</th>\n",
       "      <td>Kuwait</td>\n",
       "      <td>Kuwait City</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>98</th>\n",
       "      <td>Luxembourg</td>\n",
       "      <td>Luxembourg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>103</th>\n",
       "      <td>Maldives</td>\n",
       "      <td>Malé</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>106</th>\n",
       "      <td>Marshall Islands</td>\n",
       "      <td>Majuro</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>109</th>\n",
       "      <td>Mexico</td>\n",
       "      <td>Mexico City</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>112</th>\n",
       "      <td>Monaco</td>\n",
       "      <td>Monaco-Ville</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>116</th>\n",
       "      <td>Mozambique</td>\n",
       "      <td>Maputo</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>124</th>\n",
       "      <td>Niger</td>\n",
       "      <td>Niamey</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131</th>\n",
       "      <td>Panama</td>\n",
       "      <td>Panama City</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>Papua New Guinea</td>\n",
       "      <td>Port Moresby</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>146</th>\n",
       "      <td>San Marino</td>\n",
       "      <td>San Marino</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>147</th>\n",
       "      <td>Sao Tomé and Príncipe</td>\n",
       "      <td>São Tomé</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>153</th>\n",
       "      <td>Singapore</td>\n",
       "      <td>Singapore</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159</th>\n",
       "      <td>South Korea</td>\n",
       "      <td>Seoul</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>162</th>\n",
       "      <td>Sri Lanka</td>\n",
       "      <td>Sri Jayawardenepura Kotte</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>166</th>\n",
       "      <td>Sweden</td>\n",
       "      <td>Stockholm</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>169</th>\n",
       "      <td>Taiwan</td>\n",
       "      <td>Taipei</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>177</th>\n",
       "      <td>Tunisia</td>\n",
       "      <td>Tunis</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      name                    capital\n",
       "2                  Algeria                    Algiers\n",
       "3                  Andorra           Andorra la Vella\n",
       "14                Barbados                 Bridgetown\n",
       "17                  Belize                   Belmopan\n",
       "23                  Brazil                   Brasília\n",
       "24                  Brunei        Bandar Seri Begawan\n",
       "27                 Burundi                  Bujumbura\n",
       "47                Djibouti                   Djibouti\n",
       "67               Guatemala             Guatemala City\n",
       "70                  Guyana                 Georgetown\n",
       "88                  Kuwait                Kuwait City\n",
       "98              Luxembourg                 Luxembourg\n",
       "103               Maldives                       Malé\n",
       "106       Marshall Islands                     Majuro\n",
       "109                 Mexico                Mexico City\n",
       "112                 Monaco               Monaco-Ville\n",
       "116             Mozambique                     Maputo\n",
       "124                  Niger                     Niamey\n",
       "131                 Panama                Panama City\n",
       "132       Papua New Guinea               Port Moresby\n",
       "146             San Marino                 San Marino\n",
       "147  Sao Tomé and Príncipe                   São Tomé\n",
       "153              Singapore                  Singapore\n",
       "159            South Korea                      Seoul\n",
       "162              Sri Lanka  Sri Jayawardenepura Kotte\n",
       "166                 Sweden                  Stockholm\n",
       "169                 Taiwan                     Taipei\n",
       "177                Tunisia                      Tunis"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "world.loc[world['name'].str.slice(0, 1)==world['capital'].str.slice(0, 1),\n",
    "          ['name', 'capital']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 13. All the vowels\n",
    "\n",
    "**Equatorial Guinea** and **Dominican Republic** have all of the vowels (a e i o u) in the name. They don't count because they have more than one word in the name.\n",
    "\n",
    "**Find the country that has all the vowels and no spaces in its name.**\n",
    "\n",
    "- You can use the phrase name `NOT LIKE '%a%'` to exclude characters from your results.\n",
    "- The query shown misses countries like Bahamas and Belarus because they contain at least one 'a'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>116</th>\n",
       "      <td>Mozambique</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           name\n",
       "116  Mozambique"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "world.loc[world['name'].str.contains('[Aa]') &\n",
    "          world['name'].str.contains('[Ee]') &\n",
    "          world['name'].str.contains('[Ii]') &\n",
    "          world['name'].str.contains('[Oo]') &\n",
    "          world['name'].str.contains('[Uu]') &\n",
    "          world['name'].str.match(r'^\\S+$'),\n",
    "         ['name']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}
